﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Text.RegularExpressions;


namespace NewContracts
{
    class XAllFECExcel
    {
        private String m_filepath;
        private DateTime m_date;

        /***************************************************************************
         *  Public Interface 
         * *************************************************************************/
        public DataSet processExcel(String in_file, DateTime in_datetime)
        {
            m_filepath = in_file;
            m_date = in_datetime;

            try
            {
                //Step1 : retrieve data from All FEC
                using (DataSet dsOri = readAllFEC())
                {
                    using (DataSet dsValid = buildValidDataSet(dsOri)) 
                    {
                        using (DataSet dsNoEmpty = deleteEpmtyRows(dsValid))
                        {
                            return dsNoEmpty;
                        }
                    }
                }
            }
            catch (System.Exception ex)
            {
                throw new Exception("processAllFEC [NG]" + ex.Message);
            }
        }

        /*****************************************************************************
         *  Private Functions 
         * ***************************************************************************/
        private DataSet readAllFEC()
        {
            String sqlFindAll = "SELECT * FROM [Sheet1$]";
            String strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + m_filepath +
                ";Extended Properties='Excel 8.0;IMEX=1';";
            try
            {
                using (OleDbDataAdapter oleAdapter = new OleDbDataAdapter(sqlFindAll, strConn))
                {
                    DataSet dsOriginal = new DataSet();
                    oleAdapter.Fill(dsOriginal, "[Sheet1$]");
                    return dsOriginal;
                }
            }
            catch (System.Exception ex)
            {
                throw new Exception("readPaymentExcel Error :" + ex.Message);
            }
        }

        // Todo : can be shared function, and put into parent class
        private DataSet deleteEpmtyRows(DataSet in_ds)
        {
            try
            {
                DataSet tmpDataSet = new DataSet();
                tmpDataSet = in_ds.Copy();
                List<int> emptyRowIndex = new List<int>();
                int colIndex = 1;

                foreach (DataRow row in tmpDataSet.Tables[0].Rows)
                {
                    bool isRowsEmpty = false;
                    foreach (DataColumn col in tmpDataSet.Tables[0].Columns)
                    {
                        if (row[col].ToString() != "")
                        {
                            isRowsEmpty = false;
                            break;
                        }
                        else
                            isRowsEmpty = true;
                    }
                    if (isRowsEmpty)
                    {
                        emptyRowIndex.Add(colIndex);
                        row.Delete();
                    }
                    colIndex++;
                }
                tmpDataSet.Tables[0].AcceptChanges();

                foreach (DataRow row in  tmpDataSet.Tables[0].Rows)
                {
                    //Fill in UpdateDate
                    row["UpdateDate"] = m_date.ToShortDateString();
                }
                return tmpDataSet;
            }
            catch (System.Exception ex)
            {
                throw new Exception("deleteEmptyRows [NG] : " + ex.Message);
            }
        }

        private DataSet buildValidDataSet(DataSet in_ds)
        {
            try
            {
                DataSet tmpDataSet = new DataSet();
                tmpDataSet = in_ds.Copy();
                tmpDataSet.Tables[0].Columns.Remove("F1");
                tmpDataSet.Tables[0].Columns.Add("UpdateDate", typeof(string));

                //Delete Counterparty and CCY2(Currency)
                foreach (DataRow row in tmpDataSet.Tables[0].Rows)
                {
                    string strCounterparty = row["Counterparty"].ToString().Trim();
                    Match matchCounterparty = Regex.Match((string)strCounterparty,@"^Counterparty",RegexOptions.IgnoreCase);
                    Match matchCCY2 = Regex.Match((string)strCounterparty, @"^CCY", RegexOptions.IgnoreCase);

                    if(matchCounterparty.Success || matchCCY2.Success)
                    {
                        row.Delete();
                    }
                }
                tmpDataSet.Tables[0].AcceptChanges();       
                return tmpDataSet;
            }
            catch (System.Exception ex)
            {
                throw new Exception("buildValidPaymentDataSet [NG]" + ex.Message);
            }

        }

    }
}
